Discussion thread for official Zabbix Template DB PostgreSQL

[Semiadmin]

Hi! Do You accept here complaints about the template "PostgreSQL by Zabbix agent 2" also? There is a bug: dependent item "WAL: Segments count", Preprocessing: JSONPATH: $.write has to be JSONPATH: $.count

[anmg]

Hi! Do You accept here complaints about the template "PostgreSQL by Zabbix agent 2" also? There is a bug: dependent item "WAL: Segments count", Preprocessing: JSONPATH: $.write has to be JSONPATH: $.count

Nice. you at least got it working.
in my case it is using default password, db , user despite the fact I configured another user with pg_monitoring role.

2021/09/06 17:59:10.483412 [Postgres] Connection failed: failed to connect to `host=localhost user=postgres database=postgres`: server error (FATAL: password authentication failed for user "postgres" (SQLSTATE 28P01)).
2021/09/06 17:59:11.759618 [Postgres] Connection failed: failed to connect to `host=localhost user=postgres database=postgres`: server error (FATAL: password authentication failed for user "postgres" (SQLSTATE 28P01)).
2021/09/06 17:59:49.213176 [Postgres] Connection failed: failed to connect to `host=localhost user=postgres database=postgres`: server error (FATAL: password authentication failed for user "postgres" (SQLSTATE 28P01)).

[Murik110]

hello.
Recently deleted one of databases from Postgres.
Now it spams in log file
[Postgres] Connection failed: failed to connect to `host=localhost user=zbx_monitor database=test`: server error (FATAL: database "test" does not exist (SQLSTATE 3D000)).
Also this DB is showed in Latest data despite it doesnt exist anymore.

How to remove all data about this old DB from zabbix?
thanks

[spiceagent11]

I see that you are heading almost in the same direction as I'm going from point of view of goals and exact approaches used techniques. Because those small differences I think that we may end up with two different templates. Despite tat fact I think that it is really worth to encircle few areas and/or exchange some thoughts and/or discuss few points :P

[splitek]

If you using template and have only one DB on host then use "unlink and clear" on particular host.

If you have more DB's on host, then you should wait for automatic clearing (in LLD rule there is box "Keep lost resources period" this is the time how long data is kept - how long you need to wait). You can also disable triggers and items for this database to not get any alarms and stop data gathering.

[Murik110]

I have Template DB PostgreSQL Agent 2 with specific {$PG.URI}
So as to add a new host with Postgres DB, should I create a new template so as to be able to put a new hostname in {$PG.URI} ?
or it should be done another way?
Can someone suggest?

Thanks in advance.


p.s. Resolved, no need to reply

[splitek]

You create new host then you attach template. If in template is defined macro like {$PG.URI} then it impact this connected host. But you can go into host configuration, into "Macros" tab, click "Inherited and host macros" and find this macro. Next click "change" and put URI specific for that host. This way we set macro on host and this macro overwrites macro from template on this host.

[Murik110]

You create new host then you attach template. If in template is defined macro like {$PG.URI} then it impact this connected host. But you can go into host configuration, into "Macros" tab, click "Inherited and host macros" and find this macro. Next click "change" and put URI specific for that host. This way we set macro on host and this macro overwrites macro from template on this host.

yes, already discovered this way... thank you very much

[altair]

Hi!
I just installed Zabbix 5.0.20 and trying to use Zabbix Template DB PostgreSQL I got this error importing the template file

mport failed

• Error in query [INSERT INTO triggers (expression,priority,recovery_mode,recovery_expres sion,correlation_mode,correlation_tag,opdata,url,s tatus,discover,type,manual_close,comments,descript ion,triggerid,flags) VALUES ('{35132} > {$PG.CONFLICTS.MAX.WARN:"{#DBNAME}"}','3','0','' ,'0','','','','0','0','0','0','The primary and standby servers are in many ways loosely connected. Actions on the primary will have an effect on the standby. As a result, there is potential for negative interactions or conflicts between them.\r\nhttps://www.postgresql.org/docs/current/hot-standby.html#HOT-STANDBY-CONFLICT','DB {#DBNAME}: Too many recovery conflicts (over {$PG.CONFLICTS.MAX.WARN:"{#DBNAME}"} in 5m)','21695','2'),('{35133} > {$PG.DEADLOCKS.MAX.WARN:"{#DBNAME}"}','4','0','' ,'0','','','','0','0','0','0','','DB {#DBNAME}: Deadlock occurred (over {$PG.DEADLOCKS.MAX.WARN:"{#DBNAME}"} in 5m)','21696','2'),('{35134}<{$PG.FROZENXID_PCT_STO P.MIN.HIGH:"{#DBNAME}"}','3','0','','0','','','' ,'0','0','0','0','Preventing Transaction ID Wraparound Failures\r\nhttps://www.postgresql.org/docs/current/routine-vacuuming.html#VACUUM-FOR-WRAPAROUND','DB {#DBNAME}: VACUUM FREEZE is required to prevent wraparound (frozen XID less than {$PG.FROZENXID_PCT_STOP.MIN.HIGH:"{#DBNAME}"} %)','21697','2'),('{35135}>{$PG.LOCKS.MAX.WARN:"{ #DBNAME}"}','2','0','','0','','','','0','0','0',' 0','','DB {#DBNAME}: Number of locks is too high (over {$PG.LOCKS.MAX.WARN:"{#DBNAME}"} in 5m)','21698','2'),('{35136}>{$PG.SLOW_QUERIES.MAX. WARN:"{#DBNAME}"}','2','0','','0','','','','0',' 0','0','0','','DB {#DBNAME}: Too many slow queries (over {$PG.SLOW_QUERIES.MAX.WARN:"{#DBNAME}"} in 5m)','21699','2')] [Unknown column 'discover' in 'field list']
• SQL statement execution has failed "INSERT INTO triggers (expression,priority,recovery_mode,recovery_expres sion,correlation_mode,correlation_tag,opdata,url,s tatus,discover,type,manual_close,comments,descript ion,triggerid,flags) VALUES ('{35132} > {$PG.CONFLICTS.MAX.WARN:"{#DBNAME}"}','3','0','' ,'0','','','','0','0','0','0','The primary and standby servers are in many ways loosely connected. Actions on the primary will have an effect on the standby. As a result, there is potential for negative interactions or conflicts between them.\r\nhttps://www.postgresql.org/docs/current/hot-standby.html#HOT-STANDBY-CONFLICT','DB {#DBNAME}: Too many recovery conflicts (over {$PG.CONFLICTS.MAX.WARN:"{#DBNAME}"} in 5m)','21695','2'),('{35133} > {$PG.DEADLOCKS.MAX.WARN:"{#DBNAME}"}','4','0','' ,'0','','','','0','0','0','0','','DB {#DBNAME}: Deadlock occurred (over {$PG.DEADLOCKS.MAX.WARN:"{#DBNAME}"} in 5m)','21696','2'),('{35134}<{$PG.FROZENXID_PCT_STO P.MIN.HIGH:"{#DBNAME}"}','3','0','','0','','','' ,'0','0','0','0','Preventing Transaction ID Wraparound Failures\r\nhttps://www.postgresql.org/docs/current/routine-vacuuming.html#VACUUM-FOR-WRAPAROUND','DB {#DBNAME}: VACUUM FREEZE is required to prevent wraparound (frozen XID less than {$PG.FROZENXID_PCT_STOP.MIN.HIGH:"{#DBNAME}"} %)','21697','2'),('{35135}>{$PG.LOCKS.MAX.WARN:"{ #DBNAME}"}','2','0','','0','','','','0','0','0',' 0','','DB {#DBNAME}: Number of locks is too high (over {$PG.LOCKS.MAX.WARN:"{#DBNAME}"} in 5m)','21698','2'),('{35136}>{$PG.SLOW_QUERIES.MAX. WARN:"{#DBNAME}"}','2','0','','0','','','','0',' 0','0','0','','DB {#DBNAME}: Too many slow queries (over {$PG.SLOW_QUERIES.MAX.WARN:"{#DBNAME}"} in 5m)','21699','2')".

Some help?

[lpossamai]

Hi guys.

I'm trying to add a new DB using this template (PostgreSQl 14), but I get the following error:

2022/02/24 05:12:23.825966 received passive check request: 'pgsql.bgwriter["tcp://pg-slave-test.example.com:5432","zbx_monitor","postgres"]' from '10.0.2.166'
2022/02/24 05:12:23.826058 [1] processing update request (1 requests)
2022/02/24 05:12:23.826125 [1] adding new request for key: 'pgsql.bgwriter["tcp://pg-slave-test.example.com:5432","zbx_monitor","postgres"]'
2022/02/24 05:12:23.826161 [1] created direct exporter task for plugin 'Postgres' itemid:0 key 'pgsql.bgwriter["tcp://pg-slave-test.example.com:5432","zbx_monitor","postgres"]'
2022/02/24 05:12:23.826257 executing direct exporter task for key 'pgsql.bgwriter["tcp://pg-slave-test.example.com:5432","zbx_monitor","postgres"]'
2022/02/24 05:12:23.835451 [Postgres] Connection failed: failed to connect to `host=pg-slave-test.example.com user=zbx_monitor database=postgres`: failed SASL auth (FATAL: password authentication failed for user "zbx_monitor" (SQLSTATE 28P01)).
2022/02/24 05:12:23.835480 failed to execute direct exporter task for key 'pgsql.bgwriter["tcp://pg-slave-test.example.com:5432","zbx_monitor","postgres"]' error: 'Connection failed: failed to connect to `host=pg-slave-test.example.com user=zbx_monitor database=postgres`: failed SASL auth (FATAL: password authentication failed for user "zbx_monitor" (SQLSTATE 28P01)).'

However, the Macro on the host is correctly setup:

{$PG.DATABASE} = testdb

Why it isn't picking up the correct DB name?


Edit:

If I try to test it using the zabbix_agent2 command, I get the following:

zabbix_agent2 -t 'pgsql.bgwriter["tcp://pg-slave-test.example.com:5432","zbx_monitor","testdb"]' -c /etc/zabbix/zabbix_agent2.conf
pgsql.bgwriter["tcp://pg-slave-test.example.com:5432","zbx_monitor","testdb"][m|ZBX_NOTSUPPORTED] [Connection failed: failed to connect to `host=pg-slave-test.example.com user=zbx_monitor database=postgres`: failed SASL auth (FATAL: password authentication failed for user "zbx_monitor" (SQLSTATE 28P01)).]

So it's not using the Database name I'm providing.

Edit:

Jira Issue created.

[romans]

Hello,
I've 8 postgresql clusters on the one server on different ports. I would like to monitor all of them. I've tried to change makro {$PG.URI} from value:
tcp://localhost:5434

to:

tcp://localhost:5434, tcp://localhost:5435, tcp://localhost:5436..
but it does not work.

I've tried to full clone a template PostgreSQL Agent 2 but I'm not able to add it to the configuration because of:

Item "pgsql.archive["{$PG.URI}","{$PG.USER}","{$PG.PASSWORD}"]" already exists on "pgsql-server.xxxx.xx", inherited from another template.

I've tried to modify names of items on copy of the template PostgreSQL Agent 2 but there is still the same error:

Item "pgsql.archive["{$PG.URI}","{$PG.USER}","{$PG.PASSWORD}"]" already exists on "pgsql-server.xxxx.xx", inherited from another template.

I don't want to create next 7 hosts for monitoring 8 clusters.

Any idea?

[WendoNZ]

I haven't seen this mentioned here but the template for the original agent doesn't function at all with SElinux enabled.

I see permission denied returned from the psql command because SElinux blocks it from reading the SQL files from /var/lib/zabbix/postgresql. I've tried a couple of other locations without success and disabling SElinux isn't an option on these hosts unfortunately. I'm sure some simple SElinux changes could fix this but it's not something I've spent enough time with to even know where to start.

Another issue is you can't use complex passwords. PostgreSQL will accept them without issue when creating the zbx_monitor user but passing a complex password through throws warnings in the agent log when you turn up debugging (it doesn't mention it on normal mode).

Might be worth adding these to the docs

[romans]

Is it possible to report dropped database by Postgresql agent 2?
LLD Discovery find new created database - it's great, but when is a database dropped I didn't get the information about that.
I can see in the in the Configuration -> Hosts information:
Preprocessing failed for: { "null" : {"datname":null,"numbackends":0,"xact_commit":0 ,"x act_rollback":0,"blks_read":107715,"...
1. Failed: cannot extract value from json by path "$['smazat8'].deadlocks": no data matches the specified path

But I'm not able to make a trigger about that.

Of course I can create new makros $DBNAME1, $DBNAME2... and write the information about name of the new databases there and monitor them by trigger, but I want to monitor databases found by the LLD discovery.

[trikke76]

surprised that the template is missing something small as the db version number
made a ticket for this 4 months ago but seems no one picks it up.

https://support.zabbix.com/browse/ZBXNEXT-7097

[mdiorio]

I'd really like to see pg_stat_user_tables included here. Specifically for things like idx_scan and seq_scan, n_tup_hot_add, etc.

I'm still trying to figure out how the new zabbix 6/agent 2 templates work.